﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using Core.Model;
using Dapper;
using DapperExtensions;
using DapperExtensions.Mapper;
using log4net;

namespace DataServer.Dao
{
    public class HisDataDao : BaseDao<HisData>
    {
        private readonly ILog _log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
        private static HisDataMapper _hisDataMapper;
        private string _tablePreFix = "his_data";

        public HisDataDao()
        {
            if (_hisDataMapper == null)
            {
                _hisDataMapper = new HisDataMapper();
            }
        }

        protected override string GetTableName()
        {
            if (_hisDataMapper != null)
                return _hisDataMapper.TableName;
            return "his_data";
        }

        private string MakeTableName(DateTime month)
        {
            return _tablePreFix + month.ToString("yyyyMM");
        }
        public bool IsExitTabble(DateTime month)
        {
            string table = _tablePreFix + month.ToString("yyyyMM");
            ConnectResult result = null;
            try
            {
                result = OpenDbConnection();
                
                var res= result?.Connection.ExecuteScalar($"SELECT table_name FROM information_schema.TABLES WHERE table_name ='{table}';");
                return !(res==null || string.IsNullOrEmpty(res.ToString()));
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return false;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }

        public bool CreateTable(DateTime month)
        {
            string sql =
                $"DROP TABLE IF EXISTS `{_tablePreFix}{month:yyyyMM}`;CREATE TABLE `{_tablePreFix}{month:yyyyMM}` (`Id` varchar(40) NOT NULL,`DevId` varchar(40) DEFAULT NULL, `IsSum` tinyint(4) unsigned DEFAULT NULL,`RecValue` double DEFAULT NULL,`RecTime` datetime NOT NULL,PRIMARY KEY(`Id`,`RecTime`)) ENGINE = MyISAM DEFAULT CHARSET = utf8; ";
            ConnectResult result = null;
            try
            {
                result = OpenDbConnection();
                return result?.Connection.Execute(sql)==1;
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return false;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }
        /// <summary>
        /// 获取表中所有数据
        /// </summary>
        /// <returns></returns>
        public override List<HisData> GetEntities()
        {
            ConnectResult result = null;
            try
            {
                result = OpenDbConnection();
                // 并行模式下会报错 There is already an open DataReader associated with this Command which must be closed first
                // 原因是Dapper 中的协程
                return result?.Connection.GetAll<HisData>().ToList();
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return null;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }
        
        public List<HisDataMsg> GetMinuteReport(DateTime toTime, int num,int minute, List<string> ids)
        {
            ConnectResult result = null;
            if (ids == null || ids.Count == 0) return null;
            StringBuilder idstr = new StringBuilder();
            string table = MakeTableName(toTime);
            foreach (string id in ids)
            {
                idstr.Append(id + ",");
            }
            idstr.Remove(idstr.Length - 1, 1);
            DateTime fromTime = toTime.AddMinutes(-(num * minute));
            try
            {
                result = OpenDbConnection();
                string sql = $"SELECT a.Id,b.Name,a.DevId,c.Name as DevName,a.RecValue,a.RecTime FROM {table} a,data_item b,device c WHERE  RecTime > '{fromTime:yyyy-MM-dd HH:mm:00}' AND RecTime <=  '{toTime:yyyy-MM-dd HH:mm:00}' AND a.Id IN({idstr}) AND a.Id= b.Id  AND a.DevId=c.Id  ORDER BY RecTime";
                return result?.Connection.GetList<HisDataMsg>(sql);
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return null;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }
        public List<HisDataMsg> GetMinuteReport(DateTime time, string ids)
        {
            ConnectResult result = null;
            if (string.IsNullOrEmpty(ids)) return null;
            string table = MakeTableName(time);
            try
            {
                result = OpenDbConnection();
                string sql = $"SELECT a.Id,b.Name,a.DevId,c.Name as DevName,a.RecValue,a.RecTime FROM {table} a,data_item b,device c WHERE  RecTime = '{time:yyyy-MM-dd HH:mm:00}'  AND a.Id IN({ids}) AND a.Id= b.Id  AND a.DevId=c.Id";
                return result?.Connection.GetList<HisDataMsg>(sql);
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return null;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }
        /// <summary>
        /// 获取时间段内所有数据
        /// </summary>
        /// <param name="dateFrom"></param>
        /// <param name="dateTo"></param>
        /// <param name="ids"></param>
        /// <returns></returns>
        public List<HisDataMsg> GetMinuteReport(DateTime dateFrom,DateTime dateTo, List<string> ids)
        {
            ConnectResult result = null;
            if (ids == null || ids.Count == 0) return null;
            StringBuilder idstr = new StringBuilder();
            string tabFrom = MakeTableName(dateFrom);
            string tabTo = MakeTableName(dateTo);

            foreach (string id in ids)
            {
                idstr.Append(id + ",");
            }

            idstr.Remove(idstr.Length - 1, 1);
           
            try
            {
                result = OpenDbConnection();
                if (tabFrom == tabTo)
                {
                    string sql = $"SELECT a.Id,b.Name,a.DevId,c.Name as DevName,a.RecValue,a.RecTime FROM {tabFrom} a,data_item b,device c WHERE  RecTime >= '{dateFrom:yyyy-MM-dd HH:mm:00}' AND RecTime <=  '{dateTo:yyyy-MM-dd HH:mm:00}' AND a.Id IN({idstr}) AND a.Id= b.Id  AND a.DevId=c.Id  ORDER BY RecTime";
                    return result?.Connection.GetList<HisDataMsg>(sql);
                }
                else
                {
                    string sql0 = $"SELECT a.Id,b.Name,a.DevId,c.Name as DevName,a.RecValue,a.RecTime FROM {tabFrom} a,data_item b,device c WHERE  RecTime >= '{dateFrom:yyyy-MM-dd HH:mm:00}' AND RecTime <=  '{dateTo:yyyy-MM-dd HH:mm:00}' AND a.Id IN({idstr}) AND a.Id= b.Id  AND a.DevId=c.Id  ORDER BY RecTime";
                    List<HisDataMsg> res0= result?.Connection.GetList<HisDataMsg>(sql0);
                    string sql1 = $"SELECT a.Id,b.Name,a.DevId,c.Name as DevName,a.RecValue,a.RecTime FROM {tabTo} a,data_item b,device c WHERE  RecTime >= '{dateFrom:yyyy-MM-dd HH:mm:00}' AND RecTime <=  '{dateTo:yyyy-MM-dd HH:mm:00}' AND a.Id IN({idstr}) AND a.Id= b.Id  AND a.DevId=c.Id  ORDER BY RecTime";
                    List<HisDataMsg> res1 = result?.Connection.GetList<HisDataMsg>(sql1);
                    if (res0 != null)
                    {
                        if(res1!=null && res1.Count>0)
                            res0.AddRange(res1);
                        return res0;
                    }
                    else
                    {
                        return res1;
                    }
                }
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return null;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }
        /// <summary>
        /// 获取指定N小时报表数据
        /// </summary>
        /// <param name="dateTime">截至时</param>
        /// <param name="num">小于等于0时，获取当日所有时的报表</param>
        /// <param name="ids"></param>
        /// <returns></returns>
        public List<HisDataMsg> GetHourReport(DateTime dateTime, int num, List<string> ids)
        {
            ConnectResult result = null;
            if (ids == null || ids.Count == 0) return null;
            StringBuilder idstr = new StringBuilder();

            foreach (string id in ids)
            {
                idstr.Append(id + ",");
            }
            idstr.Remove(idstr.Length - 1, 1);
            try
            {
                result = OpenDbConnection();
                string sql;
                if (num<=0)
                    sql = $"SELECT a.Id,b.Name,a.DevId,c.Name as DevName,a.RecValue,a.RecTime FROM report_hour a,data_item b,device c WHERE RecTime >= '{dateTime:yyyy-MM-dd 00:00:00}' AND RecTime <  '{dateTime.AddDays(1):yyyy-MM-dd 00:00:00}' AND a.Id IN({idstr}) AND a.Id= b.Id AND a.DevId=c.Id ORDER BY RecTime";
                else
                    sql = $"SELECT a.Id,b.Name,a.DevId,c.Name as DevName,a.RecValue,a.RecTime FROM report_hour a,data_item b,device c WHERE RecTime >= '{dateTime.AddHours(-num):yyyy-MM-dd HH:00:00}' AND RecTime <  '{dateTime:yyyy-MM-dd HH:00:00}' AND a.Id IN({idstr}) AND a.Id= b.Id  AND a.DevId=c.Id  ORDER BY RecTime DESC";
                return result?.Connection.GetList<HisDataMsg>(sql);
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return null;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }
        /// <summary>
        /// 获取指定N日报表数据
        /// </summary>
        /// <param name="dateTime">指定月日</param>
        /// <param name="num">小于等于0时，获取当月所有日的报表</param>
        /// <param name="ids"></param>
        /// <returns></returns>
        public List<HisDataMsg> GetDayReport(DateTime dateTime,int num, List<string> ids)
        {
            ConnectResult result = null;
            if (ids == null || ids.Count == 0) return null;
            StringBuilder idstr = new StringBuilder();

            foreach (string id in ids)
            {
                idstr.Append(id + ",");
            }
            idstr.Remove(idstr.Length - 1, 1);
            try
            {
                result = OpenDbConnection();
                string sql;
                if (num <=0)
                    sql = $"SELECT a.Id,b.Name,a.DevId,c.Name as DevName,a.RecValue,a.RecTime FROM report_day a,data_item b,device c WHERE RecTime >= '{dateTime:yyyy-MM-01 00:00:00}' AND RecTime <  '{dateTime.AddMonths(1):yyyy-MM-01 00:00:00}' AND a.Id IN({idstr}) AND a.Id= b.Id  AND a.DevId=c.Id  ORDER BY RecTime";
                else
                    sql = $"SELECT a.Id,b.Name,a.DevId,c.Name as DevName,a.RecValue,a.RecTime FROM report_day a,data_item b,device c WHERE RecTime >= '{dateTime.AddDays(-num):yyyy-MM-dd 00:00:00}' AND RecTime <  '{dateTime:yyyy-MM-dd 00:00:00}' AND a.Id IN({idstr}) AND a.Id= b.Id  AND a.DevId=c.Id  ORDER BY RecTime";
                return result?.Connection.GetList<HisDataMsg>(sql);
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return null;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }

        /// <summary>
        /// 获取月数据报表
        /// </summary>
        /// <param name="dateTime">指定年月</param>
        /// <param name="num">小于等于0时，获取当年所有月的报表</param>
        /// <param name="ids"></param>
        /// <returns></returns>
        public List<HisDataMsg> GetMonthReport(DateTime dateTime,int num,List<string> ids)
        {
            ConnectResult result = null;
            if (ids == null || ids.Count == 0) return null;
            StringBuilder idstr = new StringBuilder();

            foreach (string id in ids)
            {
                idstr.Append(id + ",");
            }
            idstr.Remove(idstr.Length - 1, 1);
            try
            {
                result = OpenDbConnection();
                string sql;
                if(num<=0)
                    sql = $"SELECT a.Id,b.Name,a.DevId,c.Name as DevName,a.RecValue,a.RecTime FROM report_month a,data_item b,device c WHERE RecTime >= '{dateTime:yyyy-01-01 00:00:00}' AND RecTime <  '{dateTime.AddYears(1):yyyy-01-01 00:00:00}' AND a.Id IN({idstr}) AND a.Id= b.Id  AND a.DevId=c.Id  ORDER BY RecTime";
                else
                    sql = $"SELECT a.Id,b.Name,a.DevId,c.Name as DevName,a.RecValue,a.RecTime FROM report_month a,data_item b,device c WHERE RecTime >= '{dateTime.AddMonths(-num):yyyy-MM-01 00:00:00}' AND RecTime <  '{dateTime:yyyy-MM-01 00:00:00}' AND a.Id IN({idstr}) AND a.Id= b.Id  AND a.DevId=c.Id  ORDER BY RecTime";
                return result?.Connection.GetList<HisDataMsg>(sql);
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return null;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }
        /// <summary>
        /// 获取统计报表
        /// </summary>
        /// <param name="type">0:小时 1:日 2:月 3:年</param>
        /// <param name="dateTime"></param>
        /// <param name="ids"></param>
        /// <returns></returns>
        public List<HisStatData> GetStatisticReport(int type,DateTime dateTime, List<string> ids)
        {
            ConnectResult result = null;
            if (ids == null || ids.Count == 0) return null;
            StringBuilder idstr = new StringBuilder();

            foreach (string id in ids)
            {
                idstr.Append(id + ",");
            }
            idstr.Remove(idstr.Length - 1, 1);
            try
            {
                result = OpenDbConnection();
                string sql;
                switch (type)
                {
                    case 0: // 按小时统计
                        string table = MakeTableName(dateTime);
                        sql = "SELECT a.Id,b.Name,a.DevId,c.Name as DevName,a.MAXVALUE,a.MinValue,a.AvgValue FROM "+
                            $"(SELECT Id, DevId, MAX(RecValue) AS MaxValue, MIN(RecValue) AS MinValue, AVG(RecValue) AS AvgValue FROM {table} where RecTime >= '{dateTime:yyyy-MM-dd HH:00:00}' AND RecTime < '{dateTime.AddHours(1):yyyy-MM-dd HH:00:00}' GROUP BY Id) a,data_item b, device c WHERE " +
                            $"a.Id IN({ idstr}) AND a.Id = b.Id AND a.DevId = c.Id";
                        break;
                    case 1: // 按天统计
                        sql = "SELECT a.Id,b.Name,a.DevId,c.Name as DevName,a.MAXVALUE,a.MinValue,a.AvgValue FROM " +
                              $"(SELECT Id, DevId, MAX(RecValue) AS MaxValue, MIN(RecValue) AS MinValue, AVG(RecValue) AS AvgValue FROM report_hour where RecTime >= '{dateTime:yyyy-MM-dd 00:00:00}' AND RecTime < '{dateTime.AddDays(1):yyyy-MM-dd 00:00:00}' GROUP BY Id) a,data_item b, device c WHERE " +
                              $"a.Id IN({ idstr}) AND a.Id = b.Id AND a.DevId = c.Id";
                        break;
                    case 2: // 按月统计
                        sql = "SELECT a.Id,b.Name,a.DevId,c.Name as DevName,a.MAXVALUE,a.MinValue,a.AvgValue FROM " +
                              $"(SELECT Id, DevId, MAX(RecValue) AS MaxValue, MIN(RecValue) AS MinValue, AVG(RecValue) AS AvgValue FROM report_day where RecTime >= '{dateTime:yyyy-MM-01 00:00:00}' AND RecTime < '{dateTime.AddMonths(1):yyyy-MM-01 00:00:00}' GROUP BY Id) a,data_item b, device c WHERE " +
                              $"a.Id IN({ idstr}) AND a.Id = b.Id AND a.DevId = c.Id";
                        break;
                    case 3: // 按年报表统计
                        sql = "SELECT a.Id,b.Name,a.DevId,c.Name as DevName,a.MAXVALUE,a.MinValue,a.AvgValue FROM " +
                              $"(SELECT Id, DevId, MAX(RecValue) AS MaxValue, MIN(RecValue) AS MinValue, AVG(RecValue) AS AvgValue FROM report_month where RecTime >= '{dateTime:yyyy-01-01 00:00:00}' AND RecTime < '{dateTime.AddYears(1):yyyy-01-01 00:00:00}' GROUP BY Id) a,data_item b, device c WHERE " +
                              $"a.Id IN({ idstr}) AND a.Id = b.Id AND a.DevId = c.Id";
                        break;
                    default:
                        return null;
                }
                return result?.Connection.GetList<HisStatData>(sql);
            } 
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return null;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }

        /// <summary>
        /// 获取环比数据
        /// </summary>
        /// <param name="type">0:小时 1:日 2:月 3:年</param>
        /// <param name="dateTime"></param>
        /// <param name="ids"></param>
        /// <returns></returns>
        public List<HisCmpData> GetCmpReport(int type, DateTime dateTime, List<string> ids)
        {
            ConnectResult result = null;
            if (ids == null || ids.Count == 0) return null;
            StringBuilder idstr = new StringBuilder();

            foreach (string id in ids)
            {
                idstr.Append(id + ",");
            }
            idstr.Remove(idstr.Length - 1, 1);
            try
            {
                result = OpenDbConnection();
                string sql;
                switch (type)
                {
                    case 0:
                        string table0 = MakeTableName(dateTime);
                        string table1 = MakeTableName(dateTime.AddHours(1));
                        sql =
                            $"SELECT a.Id,b. NAME,a.DevId,c. NAME AS DevName,ROUND(a.RecValue/d.RecValue,3)as Percent From (SELECT * FROM {table1} WHERE RecTime='{dateTime.AddHours(1):yyyy-MM-dd HH:00:00}' AND Id IN({ idstr}) ) a, data_item b,device c,(SELECT * FROM {table0} WHERE RecTime='{dateTime:yyyy-MM-dd HH:00:00}'  AND Id IN({ idstr}) ) d WHERE  a.Id=d.Id AND a.Id = b.Id AND a.DevId = c.Id";
                        break;
                    case 1:
                        sql =
                            $"SELECT a.Id,b. NAME,a.DevId,c. NAME AS DevName,ROUND(a.RecValue/d.RecValue,3)as Percent From (SELECT * FROM report_hour WHERE RecTime='{dateTime.AddDays(1):yyyy-MM-dd 00:00:00}' AND Id IN({ idstr}) ) a, data_item b,device c,(SELECT * FROM report_hour WHERE RecTime='{dateTime:yyyy-MM-dd 00:00:00}'  AND Id IN({ idstr}) ) d WHERE  a.Id=d.Id AND a.Id = b.Id AND a.DevId = c.Id";
                        break;
                    case 2:
                        sql =
                            $"SELECT a.Id,b. NAME,a.DevId,c. NAME AS DevName,ROUND(a.RecValue/d.RecValue,3)as Percent From (SELECT * FROM report_day WHERE RecTime='{dateTime.AddMonths(1):yyyy-MM-01 00:00:00}' AND Id IN({ idstr}) ) a, data_item b,device c,(SELECT * FROM report_day WHERE RecTime='{dateTime:yyyy-MM-01 00:00:00}'  AND Id IN({ idstr}) ) d WHERE  a.Id=d.Id AND a.Id = b.Id AND a.DevId = c.Id";
                        break;
                    case 3:
                        sql =
                            $"SELECT a.Id,b. NAME,a.DevId,c. NAME AS DevName,ROUND(a.RecValue/d.RecValue,3)as Percent From (SELECT * FROM report_month WHERE RecTime='{dateTime.AddYears(1):yyyy-01-01 00:00:00}' AND Id IN({ idstr}) ) a, data_item b,device c,(SELECT * FROM report_month WHERE RecTime='{dateTime:yyyy-01-01 00:00:00}'  AND Id IN({ idstr}) ) d WHERE  a.Id=d.Id AND a.Id = b.Id AND a.DevId = c.Id";
                        break;
                    default:
                        return null;
                }
                return result?.Connection.GetList<HisCmpData>(sql);
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return null;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }
        /// <summary>
        /// 根据sql语句获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        protected override List<HisData> GetEntities(string sql)
        {
            ConnectResult result = null;
            try
            {
                if (string.IsNullOrEmpty(sql))
                    return null;
                result = OpenDbConnection();
                return result?.Connection.GetList<HisData>(sql);
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return null;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public bool InsertEntity(HisData entity,DateTime month)
        {
            ConnectResult result = null;
            try
            {
                if (entity == null)
                    return false;
                result = OpenDbConnection();
                return result?.Connection.Insert(entity,MakeTableName(month));
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return false;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }

        /// <summary>
        /// 批量插入数据
        /// </summary>
        /// <param name="entities"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public bool InsertEntities(List<HisData> entities,DateTime month)
        {
            IDbTransaction tran = null;
            ConnectResult result = null;
            try
            {
                if (entities == null || entities.Count == 0)
                    return false;

                result = OpenDbConnection();
                tran = result?.Connection.BeginTransaction();
                string table = MakeTableName(month);
                foreach (HisData sss in entities)
                {
                    result?.Connection.Insert(sss, table);
                }
                tran?.Commit();
                return true;
            }
            catch (Exception ex)
            {
                tran?.Rollback();
                _log.Error(ex.Message);
                return false;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }

        /// <summary>
        /// 批量插入或更新数据
        /// </summary>
        /// <param name="entities"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public bool InsertOrUpdateEntities(List<HisData> entities,DateTime month)
        {
            IDbTransaction tran = null;
            ConnectResult result = null;
            try
            {
                if (entities == null || entities.Count == 0)
                    return false;
                result = OpenDbConnection();
                tran = result?.Connection.BeginTransaction();
                string table = MakeTableName(month);
                foreach (HisData sss in entities)
                {
                    result?.Connection.InsertOrUpdate(sss, table);
                }
                tran?.Commit();
                return true;
            }
            catch (Exception ex)
            {
                tran?.Rollback();
                _log.Error(ex.Message);
                return false;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }
        /// <summary>
        /// 用历史库生成报表
        /// </summary>
        /// <param name="reporTime"></param>
        /// <param name="reportType"></param>
        /// <returns></returns>
        public bool GenerateReport(DateTime reporTime, ReportType reportType)
        {
            IDbTransaction tran = null;
            ConnectResult result = null;
            try
            {
                result = OpenDbConnection();
                tran = result?.Connection.BeginTransaction();
                DateTime date = reporTime.AddHours(-1);
                string table0= MakeTableName(date), table1 = MakeTableName(reporTime),sql0,sql1, sql2;
                switch (reportType)
                {
                    case ReportType.Hour: // 零点
                        // 即时值
                        sql0 = $"Insert into report_hour(select Id,DevId,IsSum,RecValue,RecTime from {table1} where IsSum=0 AND Day(RecTime)={reporTime.Day} and MINUTE(RecTime) = 0) ON DUPLICATE KEY UPDATE RecValue=VALUES(RecValue)";
                        result?.Connection.Execute(sql0);
                        // 累加值
                        if (reporTime.Hour == 0)// 日期变更
                        {
                            sql2 =
                                $"Insert into report_hour( select a.Id as Id ,a.DevId as DevId,a.IsSum as IsSum,(a.RecValue-b.RecValue) as RecValue,a.RecTime as RecTime\r\nfrom\r\n(select * from {table1} where IsSum=1 AND Day(RecTime)={reporTime.Day} AND HOUR(RecTime)=0 and MINUTE(RecTime) = 0) a,\r\n(select * from {table0} where IsSum=1 AND Day(RecTime)={date.Day} AND HOUR(RecTime)=23 and MINUTE(RecTime) = 0) b\r\nwhere a.Id=b.Id and DATE_ADD(b.RecTime,INTERVAL 1 HOUR) = a.RecTime) ON DUPLICATE KEY UPDATE RecValue=VALUES(RecValue)";
                            result?.Connection.Execute(sql2);
                        }
                        else
                        {
                            sql1 =
                                $"Insert into report_hour( select a.Id as Id ,a.DevId as DevId,a.IsSum as IsSum,(a.RecValue-b.RecValue) as RecValue,a.RecTime as RecTime\r\nfrom\r\n(select * from {table0} where IsSum=1 AND Day(RecTime)={reporTime.Day} and MINUTE(RecTime) = 0) a,\r\n(select * from {table0} where IsSum=1 AND Day(RecTime)={date.Day} and MINUTE(RecTime) = 0) b\r\nwhere a.Id=b.Id and DATE_ADD(b.RecTime,INTERVAL 1 HOUR) = a.RecTime) ON DUPLICATE KEY UPDATE RecValue=VALUES(RecValue)";
                            result?.Connection.Execute(sql1);
                        }
                        break;
                    case ReportType.Day:
                        sql0 = $"Insert into report_day(select Id,DevId,IsSum,RecValue,RecTime from report_hour where IsSum=0 AND MONTH(RecTime)={date.Month} AND DAY(RecTime)={date.Day} AND HOUR(RecTime)=0) ON DUPLICATE KEY UPDATE RecValue=VALUES(RecValue)";
                        sql1 =
                                $"INSERT INTO report_day(Id,DevId,IsSum,RecValue,RecTime)(SELECT Id,DevId,IsSum,SUM(RecValue) as RecValue,RecTime from report_hour WHERE IsSum=1 AND MONTH(RecTime) = {date.Month} AND  DAY(RecTime)={date.Day} GROUP BY Id,date_format(RecTime, \'%Y-%m-%d 00:00:00\') ORDER BY RecTime) ON DUPLICATE KEY UPDATE RecValue=VALUES(RecValue)";
                        result?.Connection.Execute(sql0);
                        result?.Connection.Execute(sql1);
                        break;
                    case ReportType.Month:
                        sql0 = $"Insert into report_month(select Id,DevId,IsSum,RecValue,RecTime from report_day where IsSum=0 AND YEAR(RecTime)={date.Year} AND MONTH(RecTime) = {date.Month} AND DAY(RecTime)={date.Day}  ON DUPLICATE KEY UPDATE RecValue=VALUES(RecValue)";
                        sql1 =
                                $"INSERT INTO report_month(Id,DevId,IsSum,RecValue,RecTime)(SELECT Id,DevId,IsSum,SUM(RecValue) as RecValue,RecTime from report_day WHERE IsSum=1 AND YEAR(RecTime) = {date.Year} AND MONTH(RecTime) = {date.Month} GROUP BY Id,date_format(RecTime, \'%Y-%m-01 00:00:00\') ORDER BY RecTime) ON DUPLICATE KEY UPDATE RecValue=VALUES(RecValue)";
                        result?.Connection.Execute(sql0);
                        result?.Connection.Execute(sql1);
                        break;
                    default:
                        // 即时值
                        sql0 = $"Insert into report_hour(select Id,DevId,IsSum,RecValue,RecTime from {table1} where IsSum=0 AND Day(RecTime)={reporTime.Day} and MINUTE(RecTime) = 0) ON DUPLICATE KEY UPDATE RecValue=VALUES(RecValue)";
                        result?.Connection.Execute(sql0);
                        // 累加值
                        if (reporTime.Hour == 0)// 日期变更
                        {
                            sql2 =
                                $"Insert into report_hour( select a.Id as Id ,a.DevId as DevId,a.IsSum as IsSum,(a.RecValue-b.RecValue) as RecValue,a.RecTime as RecTime\r\nfrom\r\n(select * from {table1} where IsSum=1 AND Day(RecTime)={reporTime.Day} AND HOUR(RecTime)=0 and MINUTE(RecTime) = 0) a,\r\n(select * from {table0} where IsSum=1 AND Day(RecTime)={date.Day} AND HOUR(RecTime)=23 and MINUTE(RecTime) = 0) b\r\nwhere a.Id=b.Id and DATE_ADD(b.RecTime,INTERVAL 1 HOUR) = a.RecTime) ON DUPLICATE KEY UPDATE RecValue=VALUES(RecValue)";
                            result?.Connection.Execute(sql2);
                        }
                        else
                        {
                            sql1 =
                                $"Insert into report_hour( select a.Id as Id ,a.DevId as DevId,a.IsSum as IsSum,(a.RecValue-b.RecValue) as RecValue,a.RecTime as RecTime\r\nfrom\r\n(select * from {table0} where IsSum=1 AND Day(RecTime)={reporTime.Day} and MINUTE(RecTime) = 0) a,\r\n(select * from {table0} where IsSum=1 AND Day(RecTime)={date.Day} and MINUTE(RecTime) = 0) b\r\nwhere a.Id=b.Id and DATE_ADD(b.RecTime,INTERVAL 1 HOUR) = a.RecTime) ON DUPLICATE KEY UPDATE RecValue=VALUES(RecValue)";
                            result?.Connection.Execute(sql1);
                        }
                        break;
                }
                tran?.Commit();
                return true;
            }
            catch (Exception ex)
            {
                tran?.Rollback();
                _log.Error(ex.Message);
                return false;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public override bool DeleteEntity(HisData entity)
        {
            ConnectResult result = null;
            try
            {
                result = OpenDbConnection();
                return result.Connection.Delete(entity);
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return false;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }
        /// <summary>
        /// 删除所有数据
        /// </summary>
        /// <returns></returns>
        public override bool DeleteAllEntities()
        {
            ConnectResult result = null;
            try
            {
                result = OpenDbConnection();
                return result.Connection.DeleteAll<HisData>();
            }
            catch (Exception ex)
            {
                _log.Error(ex.Message);
                return false;
            }
            finally
            {
                if (result != null)
                {
                    CloseDbConnection(result.Name);
                }
            }
        }

    }
    public sealed class HisDataMapper : ClassMapper<HisData>
    {
        public HisDataMapper()
        {
            Table("his_data");
            Map(x => x.Id).Key(KeyType.Assigned);
            Map(x => x.RecTime).Key(KeyType.Assigned);
            //Ignore this property entirely
            //optional, map all other columns
            AutoMap();
        }
    }

    public enum ReportType
    {
        Hour,
        Day,
        Month
    }
}
